{
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.12-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.6.12 64-bit ('Begin': conda)",
   "metadata": {
    "interpreter": {
     "hash": "5418aa4018a4a6d4e7a95d1864632b051ddd71a0141746edebf6fac21ca2c6f9"
    }
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "source": [
    "# Ch13. 数据库"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## 13.1 API\n",
    "\n",
    "标准数据库 API：PEP 249 中定义，也可参考[数据库编程指南](http://wiki.python.org/moin/DatabaseProgramming)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "### 13.1.1 全局变量\n",
    "\n",
    "-   apilevel：字符串常量，用于描述使用的 Python DB API 的版本，\n",
    "-   threadsafety：0~3 的整数，描述模块的线程安全程度。\n",
    "    -   0 表示线程不能共享模块\n",
    "    -   1 表示线程可以共享模块本身，但是不能共享连接\n",
    "    -   2 表示线程可以共享模块，也可以共享连接，但是不能共享游标\n",
    "    -   3 表示线程可以完成共享\n",
    "-   paramstyle：在 SQL 查询中使用的参数风格，即当你执行多个类似的数据库查询时，如何在 SQL 查询中插入参数。\n",
    "format：表示标准字符串格式设置方式(使用基本的格式编码)，在需要插入参数的地方使用 `%s`\n",
    "pyformat：表示扩展的格式编码，即旧式字典插入使用的格式编码，例如：`%(foo)s`\n",
    "qmark：表示使用问号(?)\n",
    "numeric：表示使用`:1`和`:2`这样的形式表示字段(其中的数字是参数的编号)\n",
    "named：表示使用`:foobar`这样的形式表示字段(其中`foobar`为参数名称)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "### 13.1.2 异常\n",
    "\n",
    "表13-2：Python DB API 指定的异常\n",
    "-   StandardError：所有异常的超类\n",
    "-   Warning(StandardError)：发生非致命问题\n",
    "-   Error(StandardError)：所有错误条件的超类\n",
    "-   InterfaceError(Error)：与接口(而不是数据库)相关的错误\n",
    "-   DatabaseError(Error)：与数据库相关的错误\n",
    "-   DataError(DatabaseError)：与数据相关的错误，例如：值不在合法的范围内\n",
    "-   OperationalError(DatabaseError)：数据库操作内部的错误\n",
    "-   IntegrityError(DatabaseError)：关系完整性的错误，例如：键未通过检查\n",
    "-   InternalError(DatabaseError)：数据库内部的错误，例如：游标无效\n",
    "-   ProgrammingError(DatabaseError)：用户编程错误，例如：未找到数据库表\n",
    "-   NotSupportedError(DatabaseError)：请求不支持的功能，例如：回滚"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "### 13.1.3 连接\n",
    "\n",
    "表13-3：函数 connect 的常用参数\n",
    "-   dsn：数据源的名称，具体含义由数据库确定\n",
    "-   user：用户名\n",
    "-   password：密码\n",
    "-   host：主机名\n",
    "-   database：数据库名\n",
    "\n",
    "表13-4：连接对象的方法\n",
    "-   `close()`：关闭连接对象。\n",
    "-   `commit()`：提交事务。如果所有事务都已经提交，则返回。\n",
    "-   `rollback()`：回滚还未提交的事务\n",
    "-   `cursor()`：返回连接的游标对象"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "### 13.1.3 游标\n",
    "\n",
    "表13-5：游标对象的方法\n",
    "-   `callproc(name[, params])`：使用指定的参数调用指定的数据库过程\n",
    "-   `close()`：关闭游标\n",
    "-   `execute(open[, params])`：执行一个 SQL 操作——可能指定参数\n",
    "-   `executeman(oper, pseq)`：执行指定的 SQL 操作多次，每次都在序列中提供了一组参数\n",
    "-   `fetchone()`：以序列的方式取回查询结果中的下一行；如果没有更多的行，就返回 None\n",
    "-   `fetchmany([size])`：取回查询结果中的多行，其中参数 size 的值默认为 arraysize\n",
    "-   `fetchall()`：以序列的方式取回余下的所有行\n",
    "-   `nextset()`：跑到下一个结果集\n",
    "-   `setinputsizes(sizes)`：用于为参数预定义内存区域\n",
    "-   `setoutputsize(size[, col])`：为取回大量数据而设置缓冲区长度\n",
    "\n",
    "\n",
    "表13-6：游标对象的属性\n",
    "-   description：由结果列描述组成的序列(只读)\n",
    "-   rowcount：结果包含的行数(只读)\n",
    "-   arraysize：fetchmany 返回的行数，默认为 1"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "### 13.1.4 类型\n",
    "\n",
    "表 13-7：DB API 构造函数和特殊值\n",
    "-   Date(year, month, day)：创建包含日期值的对象\n",
    "-   Time(hour, minute, second)：创建包含时间值的对象\n",
    "-   Timestamp(y, mon, d, h, min, s)：创建包含时间戳的对象\n",
    "-   DataFromTicks(ticks)：根据从新纪元开始过去的秒数创建包含日期值的对象\n",
    "-   TimeFromTicks(ticks)：：根据从新纪元开始过去的秒数创建包含时间值的对象\n",
    "-   imestampFromTicks(ticks)：：根据从新纪元开始过去的秒数创建包含时间戳的对象\n",
    "-   Binary(string)：创建包含二进制字符串的对象\n",
    "-   STRING：描述基于字符串的列(如：CHAR)\n",
    "-   BINARY：描述二进制列(如：LONG 或 RAW)\n",
    "-   NUMBER：描述数字列\n",
    "-   DATATIME：描述日期/时间列\n",
    "-   ROWID：描述行 ID 列"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## 13.2 SQLite和PySQLite\n",
    "\n"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "conn=sqlite3.connect('somedatabase.db')\n",
    "curs=conn.cursor()\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "source": [
    "### 13.2.2 例子\n",
    "\n"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建并且填充数据库表\n",
    "# 13-1：将数据导入数据库\n",
    "import sqlite3\n",
    "\n",
    "def convert(value):\n",
    "    if value.startswith('~'): return value.strip('~')\n",
    "    if not value: value='0'\n",
    "    return float(value)\n",
    "\n",
    "conn=sqlite3.connect('food.db')\n",
    "curs=conn.cursor()\n",
    "curs.execute('''\n",
    "CREATE TABLE food(\n",
    "    id      TEXT PRIMARY KEY,\n",
    "    desc    TEXT,\n",
    "    water   FLOAT,\n",
    "    kcal    FLOAT,\n",
    "    protein FLOAT,\n",
    "    fat     FLOAT,\n",
    "    ash     FLOAT,\n",
    "    carbs   FLOAT,\n",
    "    fiber   FLOAT,\n",
    "    sugar   FLOAT\n",
    ")\n",
    "''')\n",
    "query='INSERT INTO food VALUES(?,?,?,?,?,?,?,?,?,?)'\n",
    "field_count=10\n",
    "for line in open('ABBREV.txt'):\n",
    "    fields=line.split('^')\n",
    "    vals=[convert(f) for f in fields[:field_count]]\n",
    "    curs.execute(query,vals)\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT * FROM food WHERE kcal <=100 AND fiber >=10 ORDER BY sugar\nid: 09216\ndesc: ORANGE PEEL,RAW\nwater: 72.5\nkcal: 97.0\nprotein: 1.5\nfat: 0.2\nash: 0.8\ncarbs: 25.0\nfiber: 10.6\nsugar: 0.0\n\nid: 09156\ndesc: LEMON PEEL,RAW\nwater: 81.6\nkcal: 47.0\nprotein: 1.5\nfat: 0.3\nash: 0.6\ncarbs: 16.0\nfiber: 10.6\nsugar: 4.17\n\nid: 11974\ndesc: GRAPE LEAVES,RAW\nwater: 73.32\nkcal: 93.0\nprotein: 5.6\nfat: 2.12\nash: 1.65\ncarbs: 17.31\nfiber: 11.0\nsugar: 6.3\n\nid: 09231\ndesc: PASSION-FRUIT,(GRANADILLA),PURPLE,RAW\nwater: 72.93\nkcal: 97.0\nprotein: 2.2\nfat: 0.7\nash: 0.8\ncarbs: 23.38\nfiber: 10.4\nsugar: 11.2\n\n"
     ]
    }
   ],
   "source": [
    "import sqlite3,sys\n",
    "\n",
    "conn=sqlite3.connect('food.db')\n",
    "curs=conn.cursor()\n",
    "query='SELECT * FROM food WHERE kcal <=100 AND fiber >=10 ORDER BY sugar'\n",
    "print(query)\n",
    "curs.execute(query)\n",
    "names=[f[0] for f in curs.description]\n",
    "for row in curs.fetchall():\n",
    "    for pair in zip(names, row):\n",
    "        print(\"{}: {}\".format(*pair))\n",
    "    print()"
   ]
  },
  {
   "source": [
    "## 13.3 小结\n",
    "\n",
    "-   Python DB API：API 定义了一个简单的标准化接口，所有数据库包装器模块都必须遵循它\n",
    "-   连接：表示到 SQL 数据库的通信链路，使用方法 cursor 可以从连接获得游标。还可以使用连接对象来提交或者回滚事务。使用完数据库后，注意将连接关闭。\n",
    "-   游标：用于查询和查看结果。可以逐行取回查询结果，也可以一次取回许多行\n",
    "-   类型和特殊值：DB API 指定了一组构造函数和特殊值的名称。构造函数用于处理日期和时间对象、二进制对象；特殊值用于表示关系型数据库的类型\n",
    "-   SQLite：小型的嵌入式 SQL 数据库"
   ],
   "cell_type": "markdown",
   "metadata": {}
  }
 ]
}